select formmain_0131.ID,
main0013.field0002 SEEPCode,max(main0013.field0009) Initiator,max(main0013.field0008) InitiatorWWID,max(main0013.field0012) Applicant,
max(main0013.field0011) ApplicantWWID,max(main0013.field0027)  Department,
max(CONVERT(varchar(10), main0013.field0018, 20)) ActivityStart,
max(CONVERT(varchar(10), main0013.field0019, 20)) ActivityEnd,
max(main0013.field0016) Country,max(main0013.field0017) City ,max(main0013.field0004) 'WFSTatusName',max(main0013.field0005) 'PA_Code',
max(main0013.field0014) 'ActType',max(main0013.field0038) 'IO',main0013.field0034 'IONO',max(main0013.field0015) 'ActCategoryCHName',max(main0013.field0006) 'PA_ActName',max(main0013.field0023) 'HcpBudget',max(main0013.field0024) 'TotalBudget', meeting.hcpGO,meeting.internalNum,hcp.hcpNum,
 main0019.field0001  'PONumber',main0019.field0003 'POName',main0019.field0013 'PreparerWWID',main0019.field0014 'PreparerName',
main0019.field0015 'RequesterWWID',main0019.field0016 'RequesterName',main0019.field0004 'POStatus',main0019.field0022 'SupplierName',
main0019.field0019 'InvoiceApproverName',main0019.field0005 "POTotalAmount"
from formmain_0235 main0019 
left join formmain_0217 main0013 on main0019.field0034 =main0013.field0034
LEFT JOIN (
SELECT
	MEET.SEEPCode,
	SUM (CASE WHEN MEET.AttendeesType = '强生内部' OR MEET.AttendeesType = '其他' THEN num	ELSE 0 END	) 'internalNum',
	SUM (CASE WHEN MEET.AttendeesType LIKE 'HCP/GO%' THEN num ELSE 0 END) 'hcpGO'
FROM(
		SELECT		main0014.field0015 AttendeesType,	main0014.field0002 SEEPCode,	COUNT (id) num
		FROM	formmain_0215 main0014
		GROUP BY	main0014.field0002,	main0014.field0015
	) MEET GROUP BY  MEET.SEEPCode) meeting on main0013.field0002=meeting.SEEPCode
LEFT JOIN (
	SELECT	m.SEEPCode,COUNT (hcpcode) hcpNum
	FROM(	SELECT		field0013 AS 'SEEPCode',field0015 hcpcode
				FROM	formmain_0216
				GROUP BY	field0013,	field0015
		) m
	GROUP BY	m.SEEPCode
) hcp  on main0013.field0002=hcp.SEEPCode
LEFT JOIN formmain_0131 ON formmain_0131.field0001=main0019.field0001
WHERE main0019.field0001 is not null
AND formmain_0131.ID IS NULL
GROUP BY main0013.field0002,hcp.hcpNum,meeting.hcpGO,meeting.internalNum, main0019.field0001 ,main0019.field0003 ,main0019.field0013 ,main0019.field0014 ,
main0019.field0015 ,main0019.field0016 ,main0019.field0004 ,main0019.field0022 ,main0013.field0034,main0019.field0019,main0019.field0005,formmain_0131.ID
